import json
import time

import pymysql

import xxlOwner
import xxlSetting


# 拼接查询sql
def search():
    houseParam = xxlSetting.executorParams
    batchId = str(int(time.time()))

    sqlStr = ""
    for i in range(len(houseParam["section"])):
        section = houseParam["section"][i]
        max_val = houseParam["max"][i]

        sqlStr += f"(SELECT house_id, {batchId} as batch_id FROM house h where (is_del = 0) and (`section` = '{section}') limit {max_val})"

        sqlStr += " union all "

    sqlStr = sqlStr[:-11]
    houseParam["batchId"] = batchId

    # 执行 python 文件
    peocessSql(houseParam, sqlStr)


# 查询传递过来的sql
def fetch_data(sql):
    conn = pymysql.connect(
        host="43.156.247.50",  # 替换为实际的主机名
        user="root",  # 替换为实际的用户名
        password="520777",  # 替换为实际的密码
        database="house"  # 替换为实际的数据库名
    )
    cursor = conn.cursor()
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    conn.close()
    return data

# 更新数据
def update_data(batch_id, row):
    try:
        house_id = row[0]
        conn = pymysql.connect(
            host="43.156.247.50",  # 替换为实际的主机名
            user="root",  # 替换为实际的用户名
            password="520777",  # 替换为实际的密码
            database="house"  # 替换为实际的数据库名
        )
        cursor = conn.cursor()

        try:
            # 构建插入数据的 SQL 查询
            sql = "INSERT INTO batch_house_owner (batch_id, house_id) VALUE (%s, %s);"
            values = (
                batch_id,
                house_id
            )

            # 执行 SQL 查询
            cursor.execute(sql, values)
            conn.commit()
        except Exception as e:
            print(e)

        cursor.close()
        conn.close()
    except Exception as e:
        print(e)


def batchInsert(batch_id, rows):
    try:
        if (len(rows) == 0):
            print(f"批次{batch_id} 没有查到待导入的数据")
            return

        conn = pymysql.connect(
            host="43.156.247.50",  # 替换为实际的主机名
            user="root",  # 替换为实际的用户名
            password="520777",  # 替换为实际的密码
            database="house"  # 替换为实际的数据库名
        )
        cursor = conn.cursor()

        try:
            # 构建插入数据的 SQL 查询
            for row in rows:
                sql = "INSERT INTO batch_house_owner (batch_id, house_id) VALUE (%s, %s);"
                values = (batch_id, row[0])
                cursor.execute(sql, values)

            # 统一提交
            conn.commit()
        except Exception as e:
            conn.rollback()
            print(e)

        cursor.close()
        conn.close()

        print(f"批次{batch_id} 插入批次记录到数据库")
    except Exception as e:
        print(e)

# 查询全量数据并打印
def select_house(batch_id):
    sql = f"select	house_id ,	`section` as 小区,	CONCAT(block_no, '栋', unit_no, '单元', cell, '室') as 房号,	price as 总价,	unit_price as 单价,	area as 建筑面积,	CONCAT(room , '室', hall, '厅') as 房间,	floor as 楼层,	total_floor as 总楼层,	decoration as 装修 from	house where	house_id in (	select		house_id	from		batch_house_owner	where		batch_id = {
        batch_id});"
    conn = pymysql.connect(
        host="43.156.247.50",  # 替换为实际的主机名
        user="root",  # 替换为实际的用户名
        password="520777",  # 替换为实际的密码
        database="house"  # 替换为实际的数据库名
    )
    cursor = conn.cursor()
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    conn.close()
    return data

# 执行sql语句
def peocessSql(houseParam, sqlStr):
    print(f"查房源：{sqlStr}")
    data = fetch_data(sqlStr)
    batch_id = str(int(houseParam["batchId"]))

    batchInsert(batch_id, data)

    houses = select_house(batch_id)
    if len(houses) > 0:
        # 批量爬取，并入库
        if houseParam["isTest"] == False:
            xxlOwner.batchInsertOwner(houseParam, houses)
        else:
            # 打印记录
            for house in houses:
                print(f"批次{batch_id} 房源记录：{house}")


# 主函数
if __name__ == '__main__':
    search()
